﻿using DeHeng_Mes.Model.Table;
using DeHeng_Mes.Model;
using DeHeng_Mes.Utils;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Mysqlx.Crud;
using NPOI.SS.Formula.Functions;
using DeHeng_Mes.Constant;

namespace DeHeng_Mes.Dao
{
    public class ProcessDao
    {
        /// <summary>
        /// 向除胶工艺数据表中插入一行数据
        /// </summary>
        /// <param name="code"></param>
        public void InsertGlueRemovalVisionData(TraceGlueRemoval traceGlueRemoval, long tableIndex = -1)
        {
            using (var db = SqlSugarConfig.Db)
            {
                // 根据类名动态设置表名
                string tableName = tableIndex == -1 ? "trace_glue_removal_vision" : $"trace_glue_removal_vision{tableIndex}";

                // 动态映射实体类到表名
                db.MappingTables.Add("TraceGlueRemoval", tableName);

                // 插入数据到指定表
                db.Insertable(traceGlueRemoval)
                    .ExecuteCommand();
            }
        }

        /// <summary>
        /// 向除胶工艺数据表中插入一行数据
        /// </summary>
        /// <param name="code"></param>
        public void InsertGlueRemovalVisionDataList(List<TraceGlueRemoval> traceGlueRemoval, long tableIndex = -1)
        {
            using (var db = SqlSugarConfig.Db)
            {
                // 根据类名动态设置表名
                string tableName = tableIndex == -1 ? "trace_glue_removal_vision" : $"trace_glue_removal_vision{tableIndex}";

                // 动态映射实体类到表名
                db.MappingTables.Add("TraceGlueRemoval", tableName);

                // 插入数据到指定表
                db.Insertable(traceGlueRemoval).ExecuteCommand();
            }
        }

        public void InsertGlueVisionData(List<TraceGlueVision> traceGlueVision, long tableIndex = -1)
        {
            using (var db = SqlSugarConfig.Db)
            {
                // 根据索引动态生成表名
                string tableName = tableIndex == -1 ? "trace_glue_vision" : $"trace_glue_vision{tableIndex}";

                // 动态映射实体类到表名
                db.MappingTables.Add("TraceGlueVision", tableName);

                // 批量插入数据到指定表
                db.Insertable(traceGlueVision)
                    .ExecuteCommand();
            }
        }



        public void InsertFdsData(TraceFds traceFds, long tableIndex = -1)
        {
            using (var db = SqlSugarConfig.Db)
            {
                // 根据类名动态设置表名
                string tableName = tableIndex == -1 ? "trace_fds" : $"trace_fds{tableIndex}";

                // 动态映射实体类到表名
                db.MappingTables.Add("TraceFds", tableName);

                // 插入数据到指定表
                db.Insertable(traceFds)
                    .ExecuteCommand();
            }
        }

        public void InsertFdsDataList(List<TraceFds> traceFds, long tableIndex = -1)
        {
            using (var db = SqlSugarConfig.Db)
            {
                // 根据类名动态设置表名
                string tableName = tableIndex == -1 ? "trace_fds" : $"trace_fds{tableIndex}";

                // 动态映射实体类到表名
                db.MappingTables.Add("TraceFds", tableName);

                // 插入数据到指定表
                db.Insertable(traceFds)
                    .ExecuteCommand();
            }
        }

        public void InsertStudWeldData(List<TraceStudWeld> traceStudWeld, long tableIndex = -1)
        {
            using (var db = SqlSugarConfig.Db)
            {
                // 动态生成表名
                string tableName = tableIndex == -1 ? "trace_stud_weld" : $"trace_stud_weld{tableIndex}";

                // 动态映射实体类到表名
                db.MappingTables.Add("TraceStudWeld", tableName);

                // 批量插入数据到指定表
                db.Insertable(traceStudWeld)
                    .ExecuteCommand();
            }
        }


        public void InsertGlueData(TraceGlue traceGlue, long tableIndex = -1)
        {
            using (var db = SqlSugarConfig.Db)
            {
                // 动态生成表名
                string tableName = tableIndex == -1 ? "trace_glue" : $"trace_glue{tableIndex}";

                var existData = db.Queryable<TraceGlue>()
                    .AS(tableName)
                    .Where(x => x.TraceId == traceGlue.TraceId)
                    .First();
                if(existData != null)
                {
                    return;
                }
                // 动态映射实体类到表名
                db.MappingTables.Add("TraceGlue", tableName);

                // 插入数据到指定表
                db.Insertable(traceGlue)
                    .ExecuteCommand();
            }
        }

        public void InsertGlueDataList(List<TraceGlue> traceGlue, long tableIndex = -1)
        {
            using (var db = SqlSugarConfig.Db)
            {
                // 动态生成表名
                string tableName = tableIndex == -1 ? "trace_glue" : $"trace_glue{tableIndex}";

                // 动态映射实体类到表名
                db.MappingTables.Add("TraceGlue", tableName);

                // 插入数据到指定表
                db.Insertable(traceGlue)
                    .ExecuteCommand();
            }
        }


        public void InsertSpotWeldData(List<TraceSpotWeld> traceSpotWeld, long tableIndex = -1)
        {
            using (var db = SqlSugarConfig.Db)
            {
                // 动态生成表名
                string tableName = tableIndex == -1 ? "trace_spot_weld" : $"trace_spot_weld{tableIndex}";

                // 动态映射实体类到表名
                db.MappingTables.Add("TraceSpotWeld", tableName);

                // 插入数据到指定表
                db.Insertable(traceSpotWeld)
                    .ExecuteCommand();
            }
        }

        public void InsertRivetData(TraceRivet traceRivet, long tableIndex = -1)
        {
            using (var db = SqlSugarConfig.Db)
            {
                // 动态生成表名
                string tableName = tableIndex == -1 ? "trace_rivet" : $"trace_rivet{tableIndex}";

                // 动态映射实体类到表名
                db.MappingTables.Add("TraceRivet", tableName);

                // 插入数据到指定表
                db.Insertable(traceRivet)
                    .ExecuteCommand();
            }
        }

        /// <summary>
        /// 产线数采工艺保存成功次数加1
        /// </summary>
        /// <param name="fictitiousCode"></param>
        /// <param name="switchToTraceId"></param>
        /// <exception cref="ArgumentException"></exception>
        public void IncrementSaveCount(string fictitiousCode, bool switchToTraceId = false)
        {
            using (var db = SqlSugarConfig.Db)
            {
                // 检查 `fictitious_code` 参数是否有效
                if (string.IsNullOrEmpty(fictitiousCode))
                {
                    throw new ArgumentException("fictitiousCode cannot be null or empty.", nameof(fictitiousCode));
                }


                if (switchToTraceId)
                {
                    // 更新 `save_count` 字段
                    db.Updateable<OrderTrace>()
                        .AS("order_trace")
                        .SetColumns(t => t.SaveCount == t.SaveCount + 1) // save_count + 1
                        .Where(t => t.TraceId == fictitiousCode) // 条件匹配 fictitious_code
                        .ExecuteCommand();
                }
                else
                {
                    // 更新 `save_count` 字段
                    db.Updateable<OrderTrace>()
                        .AS("order_trace")
                        .SetColumns(t => t.SaveCount == t.SaveCount + 1) // save_count + 1
                        .Where(t => t.FictitiousCode == fictitiousCode) // 条件匹配 fictitious_code
                        .ExecuteCommand();
                }
            }
        }

        // 泛型查询方法
        public List<TraceStudWeld> GetStudWeldData(string fictitiousCode)
        {
            using (var db = SqlSugarConfig.Db)
            {
                var result = db.Queryable<TraceStudWeld>()
                    .Where(x => x.TraceId == fictitiousCode)
                    .ToList();
                // 这里 result 已经是 List<T> 类型，所以可以直接返回
                return result;
            }
        }

        public List<TraceGlueRemoval> GetGlueRemovalData(string fictitiousCode)
        {
            using (var db = SqlSugarConfig.Db)
            {
                var result = db.Queryable<TraceGlueRemoval>()
                    .Where(x => x.TraceId == fictitiousCode)
                    .ToList();
                // 这里 result 已经是 List<T> 类型，所以可以直接返回
                return result;
            }
        }

        public List<TraceGlue> GetGlueData(string fictitiousCode)
        {
            using (var db = SqlSugarConfig.Db)
            {
                var result = db.Queryable<TraceGlue>()
                    .Where(x => x.TraceId == fictitiousCode)
                    .ToList();
                // 这里 result 已经是 List<T> 类型，所以可以直接返回
                return result;
            }
        }

        public Dictionary<string, List<TraceSpotWeld>> GetSpotWeldDataDeep(string fictitiousCode)
        {
            using (var db = SqlSugarConfig.Db)
            {
                long secondTableIndex = HashUtils.GetHashAsInt(fictitiousCode, 10);

                // 动态生成表名
                string tableName = $"trace_parts{secondTableIndex}";

                List<string> resultList = new List<string>();


                // 动态映射实体类到表名
                db.MappingTables.Add("TraceParts", tableName);

                List<string> secondCode = db.Queryable<TraceParts>()
                                .Where(x => x.TraceId == fictitiousCode)
                                .Select(x => x.MaterialCode)
                                .ToList();
                List<string> targetCodeList = new List<string>();
                if (secondCode.Count > 0)
                {
                    foreach (var code in secondCode)
                    {
                        long thirdTableIndex = HashUtils.GetHashAsInt(code, 10);

                        tableName = $"trace_small_parts{thirdTableIndex}";
                        db.MappingTables.Add("TraceSmallParts", tableName);
                        var targetCodePart = db.Queryable<TraceSmallParts>()
                            .Where(x => x.WorkCode == code)
                            .Select(x => x.FictitiousCode)
                            .First();
                        targetCodeList.Add(targetCodePart);
                    }

                    if (targetCodeList.Count > 0)
                    {
                        var groupedResults = db.Queryable<TraceSpotWeld>()
                            .Where(x => targetCodeList.Contains(x.TraceId))
                            .ToList()
                            .GroupBy(x => x.TraceId)
                            .ToDictionary(g => g.Key, g => g.ToList());
                        return groupedResults;
                    }
                    else
                    {
                        return new Dictionary<string, List<TraceSpotWeld>>();
                    }
                }
                else
                {
                    return new Dictionary<string, List<TraceSpotWeld>>();
                }
            }
        }

        public List<TraceSpotWeld> GetSpotWeldData(string fictitiousCode)
        {
            using (var db = SqlSugarConfig.Db)
            {
                var result = db.Queryable<TraceSpotWeld>()
                    .Where(x => x.TraceId == fictitiousCode)
                    .ToList();
                // 这里 result 已经是 List<T> 类型，所以可以直接返回
                return result;
            }
        }

        public List<TraceFds> GetFdsData(string fictitiousCode)
        {
            using (var db = SqlSugarConfig.Db)
            {
                var result = db.Queryable<TraceFds>()
                    .Where(x => x.TraceId == fictitiousCode)
                    .ToList();
                // 这里 result 已经是 List<T> 类型，所以可以直接返回
                return result;
            }
        }

        public List<TraceGlueVision> GetGluingVisionData(string fictitiousCode)
        {
            using (var db = SqlSugarConfig.Db)
            {
                var result = db.Queryable<TraceGlueVision>()
                    .Where(x => x.TraceId == fictitiousCode)
                    .ToList();
                // 这里 result 已经是 List<T> 类型，所以可以直接返回
                return result;
            }
        }

        public List<TraceRivet> GetRivetingData(string fictitiousCode)
        {
            using (var db = SqlSugarConfig.Db)
            {
                var result = db.Queryable<TraceRivet>()
                    .Where(x => x.TraceId == fictitiousCode)
                    .ToList();
                // 这里 result 已经是 List<T> 类型，所以可以直接返回
                return result;
            }
        }

        public void DeleteDataByTraceId<T>(string traceId, string tableName) where T : class, ITraceId, new()
        {
            using (var db = SqlSugarConfig.Db)
            {
                // 手动设置表名并执行删除操作
                db.Deleteable<T>()
                    .AS(tableName) // 指定表名
                    .Where(x => x.TraceId == traceId) // 条件为 TraceId 匹配
                    .ExecuteCommand();
            }
        }



        public void InsertAndDeleteFdsData(string code, List<TraceFds> fdsData, long tableIndex, bool update)
        {
            using (var db = SqlSugarConfig.Db)
            {
                try
                {
                    db.Ado.BeginTran();
                    InsertFdsDataList(fdsData, tableIndex); // 插入数据
                    DeleteDataByTraceId<TraceFds>(code, "trace_fds"); // 删除数据
                    bool result = db.Queryable<TraceNotSave>()
                        .Where(x => x.TraceId == code && x.MethodName == ProcessConstant.Fds)
                        .Any();
                    if (update&&result)
                    {
                        IncrementSaveCount(code, true);
                    }
                    db.Ado.CommitTran();
                }
                catch (Exception)
                {
                    db.Ado.RollbackTran();
                    throw;
                }
            }
        }

        public void InsertAndDeleteGlueData(string code, List<TraceGlue> glueData, long tableIndex, bool update)
        {
            using (var db = SqlSugarConfig.Db)
            {
                try
                {
                    db.Ado.BeginTran();

                    InsertGlueDataList(glueData, tableIndex); // 插入数据
                    DeleteDataByTraceId<TraceGlue>(code, "trace_glue"); // 删除数据
                    bool result = db.Queryable<TraceNotSave>()
                        .Where(x => x.TraceId == code && x.MethodName == ProcessConstant.Fds)
                        .Any();
                    if (update&&result)
                    {
                        IncrementSaveCount(code);
                    }

                    db.Ado.CommitTran();
                }
                catch (Exception)
                {
                    db.Ado.RollbackTran();
                    throw;
                }
            }
        }

        public void InsertAndDeleteGlueVisionData(string code, List<TraceGlueVision> glueVisionData, long tableIndex, bool update)
        {
            using (var db = SqlSugarConfig.Db)
            {
                try
                {
                    db.Ado.BeginTran();

                    InsertGlueVisionData(glueVisionData, tableIndex); // 插入数据
                    DeleteDataByTraceId<TraceGlueVision>(code, "trace_glue_vision"); // 删除数据
                    bool result = db.Queryable<TraceNotSave>()
                        .Where(x => x.TraceId == code && x.MethodName == ProcessConstant.Fds)
                        .Any();
                    if (update&&result)
                    {
                        IncrementSaveCount(code);
                    }

                    db.Ado.CommitTran();
                }
                catch (Exception)
                {
                    db.Ado.RollbackTran();
                    throw;
                }
            }
        }

        public void InsertAndDeleteStudWeldData(string code, List<TraceStudWeld> studWeldData, long tableIndex, bool update)
        {
            using (var db = SqlSugarConfig.Db)
            {
                try
                {
                    db.Ado.BeginTran();
                    InsertStudWeldData(studWeldData, tableIndex); // 插入数据
                    DeleteDataByTraceId<TraceStudWeld>(code, "trace_stud_weld"); // 删除数据
                    bool result = db.Queryable<TraceNotSave>()
                        .Where(x => x.TraceId == code && x.MethodName == ProcessConstant.Fds)
                        .Any();
                    if (update&&result)
                    {
                        IncrementSaveCount(code, true);
                    }

                    db.Ado.CommitTran();
                }
                catch (Exception)
                {
                    db.Ado.RollbackTran();
                    throw;
                }
            }
        }

        public void InsertAndDeleteSpotWeldData(string code, List<TraceSpotWeld> spotWeldData, long tableIndex, bool update)
        {
            using (var db = SqlSugarConfig.Db)
            {
                try
                {
                    db.Ado.BeginTran();
                    InsertSpotWeldData(spotWeldData, tableIndex);
                    DeleteDataByTraceId<TraceSpotWeld>(code, "trace_spot_weld");
                    bool result = db.Queryable<TraceNotSave>()
                        .Where(x => x.TraceId == code && x.MethodName == ProcessConstant.Fds)
                        .Any();
                    if (update&&result)
                    {
                        IncrementSaveCount(code);
                    }

                    db.Ado.CommitTran();
                }
                catch (Exception)
                {
                    db.Ado.RollbackTran();
                    throw;
                }
            }
        }

        public void InsertAndDeleteGlueRemovalVisionData(string code, List<TraceGlueRemoval> glueRemovalData, long tableIndex, bool update)
        {
            using (var db = SqlSugarConfig.Db)
            {
                try
                {
                    db.Ado.BeginTran();
                    InsertGlueRemovalVisionDataList(glueRemovalData, tableIndex);
                    DeleteDataByTraceId<TraceGlueRemoval>(code, "trace_glue_removal_vision");
                    bool result = db.Queryable<TraceNotSave>()
                        .Where(x => x.TraceId == code && x.MethodName == ProcessConstant.Fds)
                        .Any();
                    if (update&&result)
                    {
                        IncrementSaveCount(code);
                    }

                    db.Ado.CommitTran();
                }
                catch (Exception)
                {
                    db.Ado.RollbackTran();

                    throw;
                }
            }
        }

        public List<string> GetSpotWeldTraceCodeDeep(string fictitiousCode)
        {
            using (var db = SqlSugarConfig.Db)
            {
                long secondTableIndex = HashUtils.GetHashAsInt(fictitiousCode, 10);

                // 动态生成表名
                string tableName = $"trace_parts{secondTableIndex}";

                // 动态映射实体类到表名（检查重复映射）
                if (!db.MappingTables.Any(mt => mt.EntityName == "TraceParts"))
                {
                    db.MappingTables.Add("TraceParts", tableName);
                }

                // 查询第二层代码 (根据虚拟总成码查询零件码)
                List<string> secondCode = db.Queryable<TraceParts>()
                    .Where(x => x.TraceId == fictitiousCode)
                    .Select(x => x.MaterialCode)
                    .ToList();

                if (secondCode == null || !secondCode.Any())
                {
                    return new List<string>(); // 返回空列表而非 null
                }

                // 批量查询第三层代码
                var thirdTableIndex = secondCode.Select(code => new
                {
                    Code = code,
                    TableName = $"trace_small_parts{HashUtils.GetHashAsInt(code, 10)}"
                });

                List<string> targetCodeList = new List<string>();

                foreach (var item in thirdTableIndex)
                {
                    // 动态映射第三张表
                    if (!db.MappingTables.Any(mt => mt.EntityName == "TraceSmallParts"))
                    {
                        db.MappingTables.Add("TraceSmallParts", item.TableName);
                    }

                    // 查询目标代码
                    var targetCodes = db.Queryable<TraceSmallParts>()
                        .Where(x => x.WorkCode == item.Code)
                        .Select(x => x.FictitiousCode)
                        .ToList();

                    targetCodeList.AddRange(targetCodes);
                }

                return targetCodeList;
            }
        }

    }
}
